Lex VOR Action Information Export

Introduction

An Export that consists of the information relative to the enquiries/vehicles that are off road.

Purpose

Lex have an export that provides information related to vehicles that are off road. This export needs to be generated by ViSN to ensure continuity in terms of process. This export will be a delta report consisting of detailed information relative to the action that is recorded against a job (enquiry) and enabling lex to monitor the progress of vehicles that are off the road."

File format

Comma Separated Values - *.csv

Terminology

• Action : The Follow up status recorded on VOR in ViSN is referred to as 'Action' by Lex
• Delta : Lex categorised this export as delta export as it is a detailed report
• Incident Type : The occurrence of an action against a enquiry is incident and the follow up status type on the action shall be treated as incident type. Further the incident type is recorded in exports with lex specific codes

Header

	Column Name	Description	Data / Format	Max Length	Mandatory
1	Record Type Indicator	Type of record, must be "VIH"	string	3	Y
2	Record Version Number	Must be "1"	number	1	Y
3	Batch Number	0000001, 9999999	number	7	Y
		Retrieve the baseReferenceDate and baseSequenceNumber from Firebase at the following location: - leasecompany {leaseId} exports settings vorActionInformationExportSequenceNumber baseReferenceDate baseSequenceNumber Determine days elapsed as number of days between the baseReferenceDate and the current date Add baseSequenceNumber to it to use it as the export sequence number

Records

S.No	Column Name	Description	Type	length	Mandatory	Example
1	Record Type Indicator	has fixed value	string	3	Y	Must be “VIR”
2	VOR ID	Enquiry Number in ViSN	number		N
		Although this field is of type varchar, enquiry number of type integer is recorded If an enquiry is moved or reassigned to another supplier, same enquiry number shall be used for both of enquiries On a given day if multiple actions recorded against the enquiry the records shall be batched with the enquiry number
3	Registration Number	VRM	string	15	N	AS12DON
4	Site ID	Customer Supplier ID	number	15	N
5	Site_Name	Supplier Site Name	string	50	N	GROUP 1 BOLTON
6	Date From	date inputted on VOR details calendar, put the time as the time manual vor enquiry was created.	datetime		N	DD/MM/YYYY HH:MM
7	Date To	vor closure	datetime		N	DD/MM/YYYY HH:MM
		Date and time when enquiry progressed to completed status or 'Vehicle Completed' is chosen as follow up status date shall be formatted to dd-MM-yyyy hh:mm and will be in 24hr format the field will be left blank for the enquiries that are not completed on the date of export
8	Driver Name	driver full name recorded while creating an enquiry	string	50	N	George Leo
		The field will be left blank if no driver information is provided when creating an enquiry
9	Category ID	Incident type reference of selected status	number		N
		Will be one of the Incident Types Incident Types Incident Type Reference Incident Type (Follow Up Status) 4010 Automatic VOR 4011 Accident collision incident 4012 Investigation of problem 4013 Manufacturer investigation 4014 Manufacturer Recall 4015 Parts delay 4016 Parts of back order 4017 Problem cannot be identified 4018 Referred to manufacturer 4019 Vehicle being quality checked 4020 Vehicle being repaired 4021 Vehicle completed 4022 Vehicle has been recovered- investigation 4023 Warranty repair being identified 4024 Workshop cannot identify the problem 4025 Workshop capacity - full 4026 Workshop not capable of fixing the vehicle 4027 Moving the vehicle to another supplier Will be treated 4010 for actions without follow up status in them
10	Category Name	VOR Notes “Status”	string	50	N	Accident Collision Incident
11	Make	Customer Contract Drawer: Make	string	50	N	BMW
12	Model	Customer Contract Drawer: Model	string	50	N	X1
13	Derivative	Customer Contract Drawer: Specification	string	50	N
14	Odometer Reading	Enquiry: Mileage	number		N	24500
15	Driver Address	Leave blank as not available from ViSN	-	-	N	-
16	Driver SMS	driver's phone number	string	20	N	0127845899
17	Driver Tel No	Leave blank as not available from ViSN	-	-	N	-
18	Driver Email	Manual Repair VOR or Booking Driver Details: Email	string	80	N	info@digitalinnk.com
19	Booking ID	enquiryNumber	number		N	5090
20	Date Created	vehicle checked-In date and time	datetime		N	DD/MM/YYYY HH:MM
		date shall be formatted to dd-MM-yyyy hh:mm and will be in 24hr format
21	Created By User ID	Leave blank as not available from ViSN	-	-	N	-
22	Created by User Name	Enquiry created by (customer) user name (Manual VOR) For Auto and Active VOR - leave blank	string	50	N	Chris Cook
23	Customer Account Number	Customer Contract Drawer: Customer Account Number	string	20	N
24	Scheme ID	Leave blank as not available from ViSN	-	-	N
25	Fleet Card Number	Leave blank as not available from ViSN	-	-	N
26	Vor Notes	Notes added in VOR notes drawer	string	1024	N
		Comments entered at time of enquiry creation or Notes added in VOR notes drawer If a user enters special characters such as commas(,), double quotes("), or single quotes(`) in a comment, the comment will be surrounded by quotation marks marking the start and end of the data in the field of csv
27	Fleet Operator Account ID	DI Admin: Customer Account Number	number		N
28	Estimated Available Date/Time	date when the action is expected to be accomplished and vehicle will be available	datetime		N	DD/MM/YYYY HH:MM
29	Chassis/Vin Number	Customer Contract Drawer: VIM	string	20	N	VD7YE2MFC12K57805
30	Your Ref	Leave blank as not available from ViSN	-	-	N	-
31	Service Centre Group Name	Supplier Group Name	string	50	N	Bellinger & Sons Vauxhall Wantage
32	Vehicle Type	Customer Contract Drawer: Vehicle Type. Vehicle Type in ViSN needs to be mapped to corresponding text code for export, but can be left blank.	string	1	N	“C” = Car “V” = LCV “H” = HGV “P” = Plant/Other

Footer

	Column Name	Description	Data / Format	Max Length	Mandatory
1	Record Type Indicator	Type of record, must be "VIT"	string	3	Y
2	Record Version Number	Must be "1"	number	1	Y
3	VIR Line Count	Number of "VIR" lines in file	number	6	Y

Data source

At the time of adding the vor note to the enquiry, a record consisting of export relative information is created at location

/leasecompany/[leaseId]/exports/vorActions/[year]/[month]/[date]/[enquiryId]

Setup Required

Database Configuration

This export is targeted for Lex lease companies alone, Hence storing these records for other companies is not required.

To prevent the records generated for non-Lex customers, A flag needs to be enabled in the lease company's export configuration to restrict the data export to Lex customers only

location: /leasecompany/[leaseId]/config/exportsConfig

attribute: vorActionExport

value: true

Generate Export

A Scheduler should be created cloud scheduler with the following parameters

• Name : Lex-GenerateDailyVorActionInformationExportCSV
• Description : Daily VOR Action Information Export Generation For Lex
• Region : europe-west2
• Frequency : 30 23 * * *
• TimeZone : Greenwhich Mean Time (GMT)
• Topic : projects/biddirect-2/topics/generateDailyVorActionInformationExport

• Message body : a JSON object including the following parameters

  • dateTimeStamp : in format yyyy-MMM-dd ex 2022-Sep-30 , when skipped it will default to current date
  • leaseId : id of the lease company that the export is desired to be generated for

Upload to SFTP

A Scheduler should be created cloud scheduler with the following parameters

• Name : Lex-UploadDailyVorActionInformationExportToSFTP
• Region : europe-west2
• Frequency : 45 23 * * *
• TimeZone : Greenwhich Mean Time (GMT)
• Topic : projects/biddirect-2/topics/uploadFileToSFTP

• Message body : a JSON object including the following parameters

  • sftpHostAddress : public hosting address of the sftp server
  • username : username for password authentication (ssh key not supported)
  • password : passkey for password authentication
  • bucketName : name of the firebase cloud storage
  • sourceFolderName : location in the firebase cloud storage to pick the file to copy to SFTP from
  • sourceFileName : regular expression that match to filename to pick file(s)
  • destinationFolderName : location in the sftp server
  • emailId : email id to receive the status in case of successful upload
  • errorEmailId : email id to receive the status in case of failed upload
  • emailSubject : generic subject that caters to both successful and failed uploads

Status: Accepted
Category: Protected
Authored By: Rama on 11 Sep, 2024

Revisions
11 Sep, 2024 - Create Documentation for vor action export, VN-14016
20 Nov, 2024 - Updated Documentation for vor action export, VN-14965 | 15004